ALTER PROCEDURE SP_SAVE_WAFER_PART_PKG_COST_DETAILS
 (
 	@fabProcessstep_id INT,
 	@Package_name    VARCHAR(50),
 	@WaferCost		NUMERIC(18,4),
 	@BGCost			NUMERIC(18,4),
 	@PkgCost		NUMERIC(18,4),
 	@testTimeInSec	NUMERIC(18,4),
 	@testCostPerSec	NUMERIC(18,4),
 	@TRCostY		NUMERIC(18,4),
 	@TRCostT		NUMERIC(18,4),
 	@bulkCost		NUMERIC(18,4),
 	@YieldAssy		NUMERIC(18,4),
 	@YieldTest		NUMERIC(18,4),
 	@Yield			NUMERIC(18,4),
 	@Raw_Wafer_Cost		NUMERIC(18,4),
 	@ZRO_EXP2_Cost		NUMERIC(18,4), 
 	@EPI_VT_Cost		NUMERIC(18,4),
 	@VN_EXP_Cost		NUMERIC(18,4),
 	@Epi_Seal_Cost		NUMERIC(18,4),
 	@Pre_Grind_Cost		NUMERIC(18,4),
 	@Sort_Cost			NUMERIC(18,4),
 	@Sort_Yield			NUMERIC(18,4), 
 	@BG_Yield			NUMERIC(18,4),
 	@MixMatch_Yield		NUMERIC(18,4), 
 	@Quarter		INT,
 	@Year			INT,
 	@user_id		VARCHAR(50)
 )
 
 AS
 BEGIN
 		DECLARE @ProcessStep_ID		INT
 		DECLARE @GDW		INT
 		DECLARE @Total_Wafer_Cost	NUMERIC(18,4)
 		DECLARE @Cumm_Cost_Per_Die	NUMERIC(18,4)
 		DECLARE @CMOS_Cumm_Cost_Per_Die NUMERIC(18,4)
 		DECLARE @MEMS_Cumm_Cost_Per_Die NUMERIC(18,4)
 		DECLARE @Process_Type VARCHAR(50)
 		DECLARE @Process_parameter VARCHAR(50)
		DECLARE @ASSM_PROCESSSTEP_ID INT
 	
 		DECLARE @ROWCOUNT INT
 		SELECT @ROWCOUNT = COUNT(*) FROM Wafer_Pkg_Cost_Table WHERE [Processstep_Id] = @fabProcessstep_id and Year = @Year AND Quarter = @Quarter
 		
 		IF @ROWCOUNT > 0
 		BEGIN
 			DELETE FROM Wafer_Pkg_Cost_Table WHERE [Processstep_Id] = @fabProcessstep_id and Year = @Year AND Quarter = @Quarter
 		END
 
 		SELECT @Process_Type = PS_TYPE_NAME FROM Process_Step WHERE ProcessStep_ID = @fabProcessstep_id
 		SELECT @Process_parameter = Parameter_Value FROM PROCESS_PARAMETER WHERE ProcessStep_Id = @fabProcessstep_id AND Parameter_Key = 'IS_MEMS_WAFER'
 		
 		IF @Process_parameter IS NOT NULL AND @Process_parameter = 'TRUE'
 		BEGIN
 			SET @Process_Type = 'MEMS'
 		END
 
 		IF( @Process_Type = 'Die Bank' )
 		BEGIN
			
 			SELECT @GDW = ISNULL( PARAMETER_VALUE, 1) FROM Process_Parameter WHERE Parameter_Key = 'FIN_GDW' AND ProcessStep_Id = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @fabProcessstep_id, 0 ) WHERE PS_TYPE = 'FAB' )
			IF( @GDW IS NULL OR @GDW < 1  )
			BEGIN
				SELECT @GDW = ISNULL( PARAMETER_VALUE, 1) FROM Process_Parameter WHERE Parameter_Key = 'Gross Die/Wafer' AND ProcessStep_Id = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @fabProcessstep_id, 0 ) WHERE PS_TYPE = 'FAB' )
			END

 			SET @Total_Wafer_Cost = ISNULL(@WaferCost,0.0) + ISNULL(@BGCost,0.0)
 			IF @Total_Wafer_Cost > 0.0
 			BEGIN
 				SET @Cumm_Cost_Per_Die = ISNULL(@Total_Wafer_Cost, 0.0)/@GDW
 			END
 			ELSE
 			BEGIN
 				SET @Cumm_Cost_Per_Die = 0.0
 			END
 		END
 		ELSE IF @Process_Type = 'ASSEMBLY' OR @Process_Type = 'Finished Goods' 
 		BEGIN
 			IF @YieldAssy = 0.0
 			BEGIN
 				SET @YieldAssy = 100.00
 			END
 			IF @YieldTest = 0.0
 			BEGIN
 				SET @YieldTest = 100.00
 			END
 			IF @Yield = 0.0
 			BEGIN
 				SET @Yield = 100.00
 			END
 
 
 			SELECT @ProcessStep_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @fabProcessstep_id, 0 ) WHERE PS_TYPE = 'Die Bank'
 			SELECT @CMOS_Cumm_Cost_Per_Die = ISNULL( Cumm_Cost_Per_Die, 0.0) FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @ProcessStep_ID AND Quarter = @Quarter AND Year = @Year
			SET @ProcessStep_ID = null
 			IF @Process_Type = 'Finished Goods' 
			BEGIN
				SELECT @ASSM_PROCESSSTEP_ID  = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @fabProcessstep_id, 0 ) WHERE PS_TYPE = 'ASSEMBLY'
	 			SELECT @ProcessStep_ID = bom_processstep_id FROM Assembly_BOM_Setup_Table WHERE assembly_processstep_id = @ASSM_PROCESSSTEP_ID
			END
			ELSE
			BEGIN
	 			SELECT @ProcessStep_ID = bom_processstep_id FROM Assembly_BOM_Setup_Table WHERE assembly_processstep_id = @fabProcessstep_id
			END
 			--SELECT @ProcessStep_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @ProcessStep_ID, 0 ) WHERE PS_TYPE = 'FAB'
 			SELECT @MEMS_Cumm_Cost_Per_Die = ISNULL( Cumm_Cost_Per_Die, 0.0) FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @ProcessStep_ID AND Quarter = @Quarter AND Year = @Year
 
 			SET @Cumm_Cost_Per_Die = ( ( ISNULL(@CMOS_Cumm_Cost_Per_Die, 0.0) + ISNULL(@MEMS_Cumm_Cost_Per_Die, 0.0) )/ (ISNULL(@YieldAssy,100.00)/100) ) + ISNULL( @PkgCost, 0.0 )
 			--PRINT @Cumm_Cost_Per_Die
 			SET @Cumm_Cost_Per_Die = ( @Cumm_Cost_Per_Die + ( ISNULL(@testCostPerSec, 0.0) * ISNULL(@testTimeInSec, 0.0) ) )/(ISNULL(@YieldTest,100.00)/100) 
 			--PRINT @Cumm_Cost_Per_Die
 			SET @Cumm_Cost_Per_Die = ( @Cumm_Cost_Per_Die + ISNULL( @bulkCost, 0.0) )/(ISNULL(@Yield,100.00)/100) 
 		END
 		ELSE IF ( @Process_Type = 'MEMS' ) 
 		BEGIN
 
 			IF @Sort_Yield = 0.0
 			BEGIN
 				SET @Sort_Yield = 100.00
 			END
 			IF @MixMatch_Yield = 0.0
 			BEGIN
 				SET @MixMatch_Yield = 100.00
 			END
 			IF @BG_Yield = 0.0
 			BEGIN
 				SET @BG_Yield = 100.00
 			END
 
 			SELECT @GDW = ISNULL( PARAMETER_VALUE, 1) FROM Process_Parameter WHERE Parameter_Key = 'FIN_GDW' AND ProcessStep_Id = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @fabProcessstep_id, 0 ) WHERE PS_TYPE = 'FAB' )
			IF( @GDW IS NULL OR @GDW < 1  )
			BEGIN
				SELECT @GDW = ISNULL( PARAMETER_VALUE, 1) FROM Process_Parameter WHERE Parameter_Key = 'Gross Die/Wafer' AND ProcessStep_Id = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @fabProcessstep_id, 0 ) WHERE PS_TYPE = 'FAB' )
			END 			
 
 			SET @Total_Wafer_Cost = ( ISNULL(@WaferCost,0.0) + ISNULL(@Raw_Wafer_Cost,0.0) + ISNULL( @ZRO_EXP2_Cost, 0.0) + ISNULL( @EPI_VT_Cost, 0.0) + ISNULL( @VN_EXP_Cost, 0.0) + ISNULL( @Epi_Seal_Cost, 0.0) + ISNULL( @Pre_Grind_Cost, 0.0) + ISNULL( @BGCost, 0.0) )/(@BG_Yield/100)
 			SET @Total_Wafer_Cost = ( @Total_Wafer_Cost + @Sort_Cost )/(@Sort_Yield/100)
 			SET @Total_Wafer_Cost = ( @Total_Wafer_Cost )/(@MixMatch_Yield/100)
 
 			IF @Total_Wafer_Cost > 0.0
 			BEGIN
 				SET @Cumm_Cost_Per_Die = ISNULL(@Total_Wafer_Cost, 0.0)/@GDW
 			END
 			ELSE
 			BEGIN
 				SET @Cumm_Cost_Per_Die = 0.0
 			END
 		END
 
 		INSERT INTO Wafer_Pkg_Cost_Table ( [Processstep_Id],[Pkg_Name],[Wafer Cost],[BackGrind Cost],[Pkg Cost],
 		[Test Time InSec],[Test Cost PerSec],[TR CostY],[TR CostT],	[Bulk],	[Yield_Assy],[Yield_Test],[Yield],
 		[Raw_Wafer_Cost] ,[ZRO_EXP2_Cost],[EPI_VT_Cost],[VN_EXP_Cost],[Epi_Seal_Cost],[Back_Grind_Yield],[Pre_Grind_Cost],[Sort_Cost],[Sort_Yield],[Mix_Match_Yield], 
 		Quarter, Year,[Timestamp], [Update_By], Cumm_Cost_Per_Die ) 
 		VALUES ( @fabProcessstep_id, @Package_name, @WaferCost, @BGCost, @PkgCost,
 				@testTimeInSec,@testCostPerSec,@TRCostY, @TRCostT, @bulkCost, @YieldAssy, @YieldTest, @Yield, 
 				@Raw_Wafer_Cost, @ZRO_EXP2_Cost, @EPI_VT_Cost, @VN_EXP_Cost, @Epi_Seal_Cost, @BG_Yield, @Pre_Grind_Cost, @Sort_Cost, @Sort_Yield,@MixMatch_Yield,
 				@Quarter, @Year, CURRENT_TIMESTAMP, @user_id, @Cumm_Cost_Per_Die ) 
 		
 		INSERT INTO Wafer_Pkg_Cost_History_Table ( [Processstep_Id],[Pkg_Name],[Wafer Cost],[BackGrind Cost],[Pkg Cost],
 		[Test Time InSec],[Test Cost PerSec],[TR CostY],[TR CostT],	[Bulk],	[Yield_Assy],[Yield_Test],[Yield],
 		[Raw_Wafer_Cost] ,[ZRO_EXP2_Cost],[EPI_VT_Cost],[VN_EXP_Cost],[Epi_Seal_Cost],[Back_Grind_Yield],[Pre_Grind_Cost],[Sort_Cost],[Sort_Yield],[Mix_Match_Yield], 
 		Quarter, Year,[Timestamp], [Update_By], Cumm_Cost_Per_Die ) 
 		VALUES ( @fabProcessstep_id, @Package_name ,@WaferCost	,@BGCost,@PkgCost,
 			@testTimeInSec,@testCostPerSec,@TRCostY ,@TRCostT ,@bulkCost,@YieldAssy ,@YieldTest,@Yield, 
 			@Raw_Wafer_Cost, @ZRO_EXP2_Cost, @EPI_VT_Cost, @VN_EXP_Cost, @Epi_Seal_Cost, @BG_Yield, @Pre_Grind_Cost, @Sort_Cost, @Sort_Yield,@MixMatch_Yield,
 			@Quarter, @Year, CURRENT_TIMESTAMP, @user_id, @Cumm_Cost_Per_Die ) 
 END
 
 

